-- create a table with a column of XML type

--Fig 13.19(a):
create table customers (
custid	number(4) primary key,
contactinfo XMLtype);


-- insert some records
--Fig 13.19(b)

insert into customers values(1,XMLtype(
'<CUSTOMER TYPE="Corporate" STATUS="Active"> 
	<NAME>WorldWide Travel Agency</NAME>
	<ADDRESS>
	<STREET>10 Main Street</STREET> 
	<CITY>New York</CITY>
	<STATE>NY</STATE>
	<ZIP>10001</ZIP>
	</ADDRESS>
	<TELEPHONE>	
	<AREACODE>212</AREACODE>
	<PHONE>123 4567</PHONE>
	</TELEPHONE>
</CUSTOMER>'));

insert into customers values(2, XMLtype(
'<CUSTOMER TYPE="Individual">  <!--start of individual customers -->
	<NAME>Mary Jones</NAME>
	<ADDRESS>
	<STREET>25 Spruce Street</STREET> 
	<CITY>San Diego</CITY>
	<STATE>CA</STATE>
	<ZIP>92101</ZIP>
	</ADDRESS>
	<TELEPHONE>
	<AREACODE>619</AREACODE>
	<PHONE>555 6789</PHONE>
	</TELEPHONE>
</CUSTOMER>'));

insert into customers values(3, XMLtype(
'<CUSTOMER TYPE="Individual" STATUS="Inactive">
	<NAME>Alice Adams</NAME>
	<ADDRESS>
<STREET>25 Orange Blossom Street</STREET> 
	<CITY>Miami</CITY>
	<STATE>FL</STATE>
	<ZIP>60601</ZIP>
	</ADDRESS>
	<TELEPHONE>
	<AREACODE>305</AREACODE>
	<PHONE>987 6543</PHONE>
	</TELEPHONE>
</CUSTOMER>'));


-- write some queries, using getClobval, then extract
-- Fig 13.19(c)
set long 500

select C.contactinfo.getClobval()as Customer
from Customers C
where custid=1;

-- Fig 13.19(d)
select C.contactinfo.extract('/CUSTOMER/NAME') as CustomerName
from Customers C;



select C.contactinfo.extract('/CUSTOMER//STATE') as CustomerStates
from Customers C;


-- Fig 13.19(e)
select C.contactinfo.extract('/CUSTOMER//STATE/text()') as CustomerStates
from Customers C;


--Fig 13.19(f)

select C.contactinfo.extract('/CUSTOMER/NAME/text()') as IndividualCustomers
from Customers C
where C.contactinfo.extract('/CUSTOMER/@TYPE').getstringval()='Individual';

















